import csv

import os

from common.utils.outputgenerater import to_excel
from config import config

user_list = [['18221156386', '三区', '张云磊'],
             ['13636630757', '三区', '丁娟'],
             ['15821368235', '三区', '虞杨'],
             ['13661814104', '三区', '张旭鸿'],
             ['18101678033', '三区', '张冰婷'],
             ['13681874317', '三区', '屠灵蒋'],
             ['18202100270', '三区', '陈启锋'],
             ['18201889704', '三区', '杨建磊'],
             ['18721159464', '三区', '于昊'],
             ['13918219954', '三区', '樊凡'],
             ['13472707118', '三区', '张志杨'],
             ['18721555685', '三区', '宋起明'],
             ['18717968391', '三区', '徐佳乐'],
             ['17821138984', '三区', '孙新武'],
             ['13569784244', '五区', '雷'],
             ['18501732119', '五区', '赵园园'],
             ['13918975630', '五区', '吴东元'],
             ['18616747755', '五区', '石凯胜'],
             ['13661403071', '五区', '余磊'],
             ['13148128666', '五区', '汪志远'],
             ['18621732667', '五区', '徐文洁'],
             ['13917974089', '五区', '董智慧'],
             ['13122020101', '五区', '周康'],
             ['18516055375', '五区', '李言'],
             ['15002145896', '五区', '丁素文'],
             ['18521508152', '五区', '杨孟智'],
             ['17612179955', '五区', '杨泉'],
             ['13122157222', '五区', '唐霓'],
             ['13167050711', '五区', '吴辉'],
             ['18721992130', '五区', '陈凯'],
             ['13122208866', '五区', '李俊坤'],
             ['13636385591', '五区', '刘宇阳'],
             ['13122135333', '五区', '单晨展'],
             ['13122341888', '五区', '余开栋'],
             ['15317288868', '五区', '葛飞'],
             ['13472704742', '五区', '林雪'],
             ['18512150512', '五区', '雷玉莹'],
             ['18702116264', '五区', '王仁义'],
             ['13637089703', '五区', '何筠'],
             ['13764776745', '五区', '赵齐齐'],
             ['15921393143', '五区', '金兵'],
             ['13003230983', '五区', '李明真'],
             ['18521782191', '五区', '杨谭松'],
             ['13918479298', '五区', '陆明娟'],
             ['13651825857', '五区', '丁艳'],
             ['13774476389', '五区', '沈标'],
             ['18602129102', '五区', '蒋壮博'],
             ['13671605513', '五区', '程小龙'],
             ['13482033114', '五区', '魏晓娟'],
             ['13472783772', '五区', '杨瑞含'],
             ['15900540276', '五区', '汪许生'],
             ['13482311514', '五区', '于波'],
             ['18616056510', '五区', '陈振国'],
             ['15618967319', '五区', '张彩霞'],
             ['13248326806', '五区', '赵腾飞'],
             ['18602126729', '五区', '张成'],
             ['13636336402', '五区', '魏兰兰'],
             ['13391018063', '五区', '周嘉琳'],
             ['18521726586', '五区', '曹莲英'],
             ['18602120035', '五区', '秦贤来'],
             ['13472779131', '五区', '彭亮'],
             ['18521710532', '五区', '冯喜梅'],
             ['15021989320', '五区', '汪亚丽'],
             ['13122188811', '五区', '李聪'],
             ['13916939849', '五区', '郑瑞然'],
             ['18221085194', '五区', '占磊'],
             ['13817626410', '五区', '王利枝'],
             ['18621040101', '二区', '汪倩'],
             ['18217393308', '二区', '公冶凡轩'],
             ['13916840167', '二区', '刘芙兵'],
             ['19901783149', '二区', '付于田'],
             ['13127723517', '二区', '罗尼'],
             ['18930706628', '二区', '汪子睿'],
             ['18017246963', '二区', '张祝星'],
             ['18930500521', '二区', '徐海云'],
             ['13166169956', '二区', '王函'],
             ['15021328552', '二区', '郑美琼'],
             ['13817352064', '二区', '许婷'],
             ['15821123041', '二区', '崔玲'],
             ['15821529937', '二区', '江勇辉'],
             ['13482162767', '二区', '张锐'],
             ['18616263558', '二区', '张素平'],
             ['18516245203', '二区', '王'],
             ['17521710626', '二区', '余发军'],
             ['15026671273', '二区', '郭传敏'],
             ['13816250180', '二区', '丁学志'],
             ['13310108595', '二区', '刘晓旭'],
             ['13564317926', '二区', '崔婷婷'],
             ['19921151164', '二区', '闵文达']
             ]

user_dict = {}
for u in user_list:
    user_dict[u[0]] = u

headers = {'姓名': 0,
           '手机号': 1,
           '来源': 2,
           '申请城市': 3,
           '学历': 4,
           '户籍': 5,
           '年龄': 6,
           '婚姻': 7,
           '身份证': 8,
           '性别': 9,
           '毕业院校': 10,
           '客户公司': 11,
           '申请额度': 12,
           '来源类型': 13,
           '名下房产情况': 14,
           '名下车产情况': 15,
           '汽车类型': 16,
           '汽车归属地': 17,
           '汽车使用年限': 18,
           '汽车价值': 19,
           '车险公司': 20,
           '当前保单缴费次数': 21,
           '车辆状态': 22,
           'BD有无保单': 23,
           '保单公司': 24,
           '年缴费额': 25,
           '保单状态': 26,
           '已缴次数': 27,
           '两年内最严重的逾期': 28,
           '一年内最严重的逾期': 29,
           '半年内最严重的逾期': 30,
           '微粒贷额度': 31,
           '芝麻信用分': 32,
           '信用卡情况': 33,
           '贷记卡总额总额度': 34,
           '贷记卡当前使用额度': 35,
           '贷记卡近半年使用额度': 36,
           '单张贷记卡最高负债率': 37,
           '有无信用贷款': 38,
           '客户星级': 39,
           '状态': 40,
           '备注': 41,
           'ID': 42,
           '员工账号': 43
           }


def readfile():
    csv_reader = csv.reader(open(r"E:\gather_tmp\汇总\汇总\再分配客户.csv"))
    users = {}
    title = None
    for row in csv_reader:
        if title == None:
            title = row
            continue
        phone = row[headers["员工账号"]]
        u = user_dict.get(phone)
        if not u:
            u = ["", "", "", ]
        if phone not in users.keys():
            users[phone] = []
        users[phone].append(row)
        row.append(u[1])
        row.append(u[2])

    for uu in users.keys():
        uus = users.get(uu)
        u = user_dict.get(uu)
        if not u:
            u = ["", "", "", ]
        filename = u[1] + "_" + u[2] + "_再分配客户.xls"
        to_excel(r"E:\gather_tmp\汇总\汇总" + os.path.sep + filename) \
            .add_sheet_by_list(headers.keys(), uus, sheetname='sheet1').build()
        print(uu)


def 合并文件():
    type = "再分配客户"
    with open(r'E:\gather_tmp\我的客户\\' + type + '.csv', "w", newline="", encoding="utf-8") as datacsv:
        csvwriter = csv.writer(datacsv)

        csvwriter.writerow(headers)
        for filename in os.listdir(r'E:\gather_tmp\我的客户'):

            if not filename.startswith(type):
                continue
            手机号 = filename.replace(type, "").replace(".csv", "").replace("(2)", "").replace(" ", "")
            try:
                csv_reader = csv.reader(open(r'E:\gather_tmp\我的客户\\' + filename, encoding="utf-8"))
                for row in csv_reader:
                    row.append("--")
                    row.append("--")
                    row.append(手机号)
                    csvwriter.writerow(row)
            except:
                print(filename)
            datacsv.flush()


# 合并文件()
readfile()
